package cn.ztuo.bitrade.dao;

import cn.ztuo.bitrade.dao.base.BaseDao;
import cn.ztuo.bitrade.entity.CesspitRecord;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CesspitRecordDao extends BaseDao<CesspitRecord> {
    CesspitRecord findById(Long id);
    List<CesspitRecord> findByMemberIdAndStatusIn(Long id, List<Long> status);
    CesspitRecord findByMemberIdAndStatus(Long id, Long status);


    @Query(value = "select ifnull(sum(hashrate),0) as hashrate from cesspit_record where status = 1",nativeQuery = true)
    String[] sumHashRate();

    @Query(value = "select ifnull(lefttotal,0) as hashrate from cess_total where member_id=:ids",nativeQuery = true)
    String[] getLeftHashRate(@Param("ids") Long ids);

    @Query(value = "select ifnull(righttotal,0) as hashrate from cess_total where member_id=:ids",nativeQuery = true)
    String[] getRightHashRate(@Param("ids") Long ids);

    @Query(value = "select ifnull(sum(number),0) as amount from cesspit_record where status = 1 and member_id=:ids",nativeQuery = true)
    String[] sumAmount(@Param("ids") Long ids);


    @Query(value = "select   a.id as id,'' as image,0 as cesspit_id,'' as name,'ODF' as coin,0 as number,0 as earning,0 as day,0 as now_day,now() as create_time,1 as status,a.member_id,a.user_name,(case when lefttotal >= righttotal then  righttotal else lefttotal end) * c.community   as hashrate from cesspit_record a inner join cess_total b on a.member_id=b.member_id inner join cesspit c on a.cesspit_id = c.id  where a.status=1 order by (case when lefttotal >= righttotal then  righttotal else lefttotal end) * c.community desc limit 100",nativeQuery = true)
    List<CesspitRecord> DONGTAIHashrate();

    @Query(value = "select   sum((case when lefttotal >= righttotal then  righttotal else lefttotal end) * c.community)   as hashrate from cesspit_record a inner join cess_total b on a.member_id=b.member_id inner join cesspit c on a.cesspit_id = c.id  where a.status=1",nativeQuery = true)
    String[] getTotalHashRate();


}
